package com.crm.dao.after_sale;

import java.util.List;

import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;

import com.crm.pojo.ClientCare;
import com.crm.vo.ClientCareAndContact;

public interface ClientCareDao extends CrudRepository<ClientCare, Long> {

	/**
	 * 通过客户关环编号查询没有被删除的客户关怀
	 * @param careid
	 * @return
	 */
	@Query("from ClientCare c where c.clientCareIsDel != '是' and c.careid = ?1")
	public ClientCare findOneIsNotDel(Long careid);
	
	/**
	 * 分页查询所有的没有被删除的客户关怀
	 * @param pageable
	 * @return
	 */
	@Query("select new com.crm.vo.ClientCareAndContact(clientCare, contact) from ClientCare clientCare ,Contact contact "
			+ "where clientCare.clientId = contact.contactid and clientCare.clientCareIsDel != '是' order by clientCare.careDate desc")
	public List<ClientCareAndContact> findAllNotDelByPage(Pageable pageable);
	
	/**
	 * 分页查询所有的没有被删除的客户关怀的数量
	 * @return
	 */
	@Query("select count(*) from ClientCare clientCare ,Contact contact "
			+ "where clientCare.clientId = contact.contactid and clientCare.clientCareIsDel != '是'")
	public Long countAllNotDelByPage();
	
	/**
	 * 根据客户关怀编号查询没有被删除的客户关怀
	 * @param careid
	 * @param pageable
	 * @return
	 */
	@Query("select new com.crm.vo.ClientCareAndContact(clientCare, contact) from ClientCare clientCare ,Contact contact "
			+ "where clientCare.clientId = contact.contactid and clientCare.clientCareIsDel != '是' and clientCare.careid = ?1 order by clientCare.careDate desc")
	public List<ClientCareAndContact> findCareByCareid(Long careid, Pageable pageable);
	
	/**
	 * 根据客户关怀编号查询没有被删除的客户关怀的数量
	 * @param careid
	 * @return
	 */
	@Query("select count(*) from ClientCare clientCare ,Contact contact "
			+ "where clientCare.clientId = contact.contactid and clientCare.clientCareIsDel != '是' and clientCare.careid = ?1 ")
	public Long countCareByCareid(Long careid);
	
	/**
	 * 根据客户关怀主题查询没有被删除的客户关怀
	 * @param careTheme
	 * @param pageable
	 * @return
	 */
	@Query("select new com.crm.vo.ClientCareAndContact(clientCare, contact) from ClientCare clientCare ,Contact contact "
			+ "where clientCare.clientId = contact.contactid and clientCare.clientCareIsDel != '是' and clientCare.careTheme like ?1 order by clientCare.careDate desc")
	public List<ClientCareAndContact> findCareByCareTheme(String careTheme, Pageable pageable);
	
	/**
	 * 根据客户关怀主题查询没有被删除的客户关怀的数量
	 * @param careTheme
	 * @return
	 */
	@Query("select count(*) from ClientCare clientCare ,Contact contact "
			+ "where clientCare.clientId = contact.contactid and clientCare.clientCareIsDel != '是' and clientCare.careTheme like ?1 ")
	public Long countCareByCareTheme(String careTheme);
}